﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using MCDirectBroadcast.Entity;
using System.Data.SqlServerCe;

namespace MCDirectBroadcast
{

	public static class SQLCeHelper
	{

		public static List<Story> Stories()
		{
			DataSet ds = ExeStorySet();
			if (ds != null && ds.Tables.Count > 0)
			{

				List<Story> list = new List<Story>();

				foreach (DataRow row in ds.Tables[0].Rows)
				{
					list.Add(new Story()
					{
						ID = Convert.ToInt32(row["ID"]),
						Title = row["Title"] as string,
						TimeToGo = row["TimeToGo"] as string,
						Location = row["Location"] as string,
						Content = row["Content"] as string,
						Owner = row["Owner"] as string,
						CreateDate = Convert.ToDateTime(row["Date"].ToString())
					});
				}

				return list;
			}

			return null;
		}

		public static DataSet ExeStorySet()
		{

			DataSet ds = new DataSet();

			try
			{
				using (var sql = GetConnection())
				{
					sql.Open();
					using (var cmd = sql.CreateCommand())
					{
						cmd.CommandText = "SELECT * FROM tbl_Sty ORDER BY ID DESC";
						SqlCeDataAdapter adp = new SqlCeDataAdapter(cmd);
						adp.Fill(ds);
					}
				}
			}
			catch (SqlCeException e)
			{
				// Log here
			}

			return ds;
		}

		public static int InsertStory(Story story)
		{
			try
			{
				using (var sql = GetConnection())
				{
					using (var cmd = sql.CreateCommand())
					{
						sql.Open();

						cmd.CommandText = @"insert into tbl_Sty(Title, TimeToGo, Location, Owner, Content, Date)
                                            values(@Title, @TimeToGo, @Location, @Owner, @Content, @Date)";
						cmd.Parameters.Add("@Date", SqlDbType.DateTime).Value = story.CreateDate;
						cmd.Parameters.Add("@Title", SqlDbType.NText).Value = story.Title;
						cmd.Parameters.Add("@TimeToGo", SqlDbType.NVarChar).Value = story.TimeToGo;
						cmd.Parameters.Add("@Location", SqlDbType.NVarChar).Value = story.Location;
						cmd.Parameters.Add("@Owner", SqlDbType.NVarChar).Value = story.Owner;
						cmd.Parameters.Add("@Content", SqlDbType.NText).Value = story.Content;
						return cmd.ExecuteNonQuery();
					}
				}
			}
			catch (SqlCeException e)
			{
				// Log here
			}
			return -1;
		}

		public static int UpdateStory(Story story)
		{
			try
			{
				using (var sql = GetConnection())
				{
					using (var cmd = sql.CreateCommand())
					{
						sql.Open();

						cmd.CommandText = @"update tbl_Sty set Title=@Title, TimeToGo=@TimeToGo, Location=@Location, Owner=@Owner, Content=@Content, Date=@Date where ID=@ID";
						cmd.Parameters.Add("@ID", SqlDbType.Int).Value = story.ID;
						cmd.Parameters.Add("@Title", SqlDbType.NText).Value = story.Title;
						cmd.Parameters.Add("@TimeToGo", SqlDbType.NVarChar).Value = story.TimeToGo;
						cmd.Parameters.Add("@Location", SqlDbType.NVarChar).Value = story.Location;
						cmd.Parameters.Add("@Owner", SqlDbType.NVarChar).Value = story.Owner;
						cmd.Parameters.Add("@Content", SqlDbType.NText).Value = story.Content;
						cmd.Parameters.Add("@Date", SqlDbType.DateTime).Value = story.CreateDate;
						return cmd.ExecuteNonQuery();
					}
				}
			}
			catch (SqlCeException e)
			{
				// Log here
			}
			return -1;
		}

		public static int DeleteStory(int storyId)
		{
			try
			{
				using (var sql = GetConnection())
				{
					using (var cmd = sql.CreateCommand())
					{
						sql.Open();
						cmd.CommandText = @"delete from tbl_Sty where ID=@ID";
						cmd.Parameters.Add("@ID", SqlDbType.Int).Value = storyId;
						return cmd.ExecuteNonQuery();
					}
				}
			}
			catch (SqlCeException e)
			{
				// Log here
			}
			return -1;
		}

		private static SqlCeConnection GetConnection()
		{
			string db = ConfigurationManager.AppSettings["SQLCeDb"];
			string pasw = ConfigurationManager.AppSettings["SQLCePasw"];

			string con = "Data Source=" + AppDomain.CurrentDomain.BaseDirectory + "\\DBs\\mcput.db.sdf;Password=" + pasw + ";Persist Security Info=False;";

			return new SqlCeConnection(con);
		}
	}
}
